package bounjour;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Vector;

/**
 *
 * Description: This class is the general manager class for accessing MySQL DB
 * 
 * @author: Senem Beken
 * @date: 07.05.2011
 */

public class RecordManager 
{

	private static String DRIVER = "com.mysql.jdbc.Driver";
	private static String URL = "jdbc:mysql://localhost/bounjour?user=root";
	private static String password = "";
	private static String username = "root";

	private Connection connection;
	private Statement statement = null;

	// constructor #1
	@SuppressWarnings("static-access")
	public RecordManager(String driver, String url, String username, String password) 
	{
		this.DRIVER = driver;
		this.URL = url;
		try 
		{
			openConnection(driver, url, username, password);
		} 
		catch (java.lang.Exception e) 
		{
			System.out.println("Error connecting to database");
		}
	}

	// constructor #2 (our actual constructor for now)
	public RecordManager() 
	{
		this(DRIVER, URL, username, password);
	}

	public void closeConnection() 
	{
		try 
		{
			if (connection != null)
				connection.close();
		} 
		catch (Exception e) 
		{
			System.err.println("Connection could not be closed:" + e);
		}
	}

	/**
	 * connection done to DB is established by URL and driver information the connection
	 * this function is called by constructor
	 */
	public void openConnection(String driver, String url, String username, String password) throws Exception 		
	{
		Class.forName(DRIVER);
		connection = DriverManager.getConnection(URL, username, password);
		statement = connection.createStatement();
		statement.execute("use bounjour;");
	}

	/*
	 * this functions is for inserting a single data to given table with given
	 * values
	 * 
	 * @param1:parameter list
	 * @param2:database target table
	 * @param3:database table column list 
	 * @returns number of inserted rows
	 */
	public int insertSingleRecord(ArrayList<String> valueList, String tableName, ArrayList<String> columnList) throws Exception 
	{
		// parse values into values string
		String valueString = "";
		int valuesLength = valueList.size();
		for (int j = 0; j < valuesLength; j++) 
		{
			valueString += "'" + valueList.get(j) + "'";
		
			if (j < (valuesLength - 1)) // place "," between parameter list
				valueString += ",";
		}
		
		// parse column list into columns string
		String columnString =""; 
		int columnsLength = columnList.size();
		for (int j = 0; j < columnsLength; j++) 
		{
			columnString += "'" + columnList.get(j) + "'";
		
			if (j < (columnsLength - 1)) // place "," between parameter list
				columnString += ",";
		}

		return insertRecord(tableName, columnString, valueString);
	}
	
	@SuppressWarnings("rawtypes")
	/*
	 * */
	public Vector selectSingleRow(String selectionCriteria, String tableName, String columnName) throws SQLException
	{
		String sqlQuery = " SELECT " + columnName + " from " + tableName + " WHERE " + columnName + " = " + selectionCriteria;
		return getRecordVector(sqlQuery);
	}
	
	/*statement can be insert, update or delete*/
	public int updateRecord(String sqlQuery) throws Exception 
	{
		return statement.executeUpdate(sqlQuery);
	}

	private int insertRecord(String tableName, String columnString, String valueString) throws SQLException 
	{
		String sqlQuery = "INSERT INTO " + tableName + " (" + columnString + ") VALUES(" + valueString + ")";
		return statement.executeUpdate(sqlQuery);
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Vector getRecordVector(String sqlQuery) 
	{
		ResultSet valueResultSet = null;
		Vector recordList = new Vector();

		try 
		{
			valueResultSet = statement.executeQuery(sqlQuery);
			ResultSetMetaData metaData = valueResultSet.getMetaData();

			int columnNum = metaData.getColumnCount();

			while (valueResultSet.next()) 
			{
				HashMap hashmap = new HashMap();
				for (int i = 1; i <= columnNum; i++)
					hashmap.put(metaData.getColumnName(i), valueResultSet.getObject(i));

				recordList.add(hashmap);
			}

			if (valueResultSet != null)
				valueResultSet.close();
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}

		return recordList;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Vector getRecordVector(int columnIndex, String sqlQuery) 
	{
		ResultSet valueResultSet = null;
		Vector recordList = new Vector();
		Statement statement = null;

		try 
		{
			statement = this.connection.createStatement();
			valueResultSet = statement.executeQuery(sqlQuery);
			Vector row = new Vector();

			while (valueResultSet.next())
				row.add(valueResultSet.getObject(columnIndex));

			recordList.add(row);
			if (valueResultSet != null)
				valueResultSet.close();

			if (statement != null)
				statement.close();
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}

		return recordList;
	}
}